package org.compiere.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class Mhrmreviewline extends X_hrm_review_line {

	/**
	 * 
	 */
	private static final long serialVersionUID = -1486753163971113511L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmreviewline.class);

	public Mhrmreviewline(Properties ctx) {
		super(ctx);
		// 
	}
	
	public Mhrmreviewline(Properties ctx, int hrm_review_line_ID, String trxName) {
		super(ctx, hrm_review_line_ID, trxName);
		// 
	}
	
	public Mhrmreviewline(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
		// 
	}
	
	/**
	 * Search for entries in the review line
	 * 
	 * @param C_BPartner_ID
	 * @param Timestamp dt
	 * @return  Model review line
	 */
	public static Mhrmreviewline getReviewline(int C_BPartner_ID, Timestamp dt) {
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmreviewline revline = new Mhrmreviewline(Env.getCtx(), 0, null);
			
		// 
		String sql_ = "SELECT * FROM HRM_REVIEW_LINE \n " +
						"WHERE ISACTIVE='Y' \n " +
							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							" AND STARTDATE BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				
				rs = pstmt.executeQuery();	
				if(rs.next())
					revline = new Mhrmreviewline(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return revline;
		
	}	
	
	public static Mhrmreviewline[] getReviewArray(int C_BPartner_ID, Timestamp dt) {
		
		if (C_BPartner_ID == 0)
			return null;
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmreviewline result = new Mhrmreviewline(Env.getCtx(), 0, null);
		
		ArrayList<Mhrmreviewline> list = new ArrayList<Mhrmreviewline>();
			
		// 
		String sql_ = "SELECT * FROM HRM_REVIEW_LINE \n " +
						"WHERE ISACTIVE='Y' \n " +
							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							" AND STARTDATE BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);				
				rs = pstmt.executeQuery();	
				while(rs.next()){
					result = new Mhrmreviewline(Env.getCtx(), rs, null);
					list.add(result);
				}					
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return list.toArray(new Mhrmreviewline[list.size()]);		
	}
	
	/**
	 * Search for entries in the review line
	 * 
	 * @param C_BPartner_ID
	 * @param Timestamp dt
	 * @return  Model review line
	 */
	public static Mhrmreviewline[] getRevLineArray(int C_BPartner_ID, Timestamp dt) {
		
		if (C_BPartner_ID == 0)
			return null;
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmreviewline result = null;
		
		ArrayList<Mhrmreviewline> list = new ArrayList<Mhrmreviewline>();
			
		// 
		String sql_ = "SELECT T.* FROM HRM_REVIEW_LINE T \n " +
				      "JOIN HRM_REVIEW R ON R.HRM_REVIEW_ID=T.HRM_REVIEW_ID \n" +
						" WHERE T.ISACTIVE='Y' \n " +
							" AND T.AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							" AND T.C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							" AND R.DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				
				rs = pstmt.executeQuery();	
				while(rs.next()){
					result = new Mhrmreviewline(Env.getCtx(), rs, null);
					list.add(result);					
				}				
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return list.toArray(new Mhrmreviewline[list.size()]);		
	}
	
	/**
	 * Search for entries in the -->>> reviewLine <<---
	 * 
	 * @param C_BPartner_ID
	 * @param Timestamp dt
	 * @return  Model review line
	 */
	public static X_hrm_reviewline getRevewLineAcct(int review_line_ID, int reservesectional_ID) {
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_hrm_reviewline result = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_REVIEWLINE WHERE HRM_REVIEW_LINE_ID= " + review_line_ID +" \n" +
				" AND HRM_RESERVESECTIONAL_ID=" +reservesectional_ID;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					result = new X_hrm_reviewline(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return result;		
	}
	
	/**
	 * Search for entries in the review line
	 * 
	 * @param HRM_COMPENSATIONTYPE=3
	 * @param Timestamp dt
	 * @return  Model review line
	 */
	public static Mhrmreviewline[] getRevLineArray(Timestamp dt) {
				
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmreviewline result = null;
		
		ArrayList<Mhrmreviewline> list = new ArrayList<Mhrmreviewline>();
			
		// 
		String sql_ = "SELECT * FROM HRM_REVIEW_LINE \n " +
				        " JOIN HRM_REVIEW ON HRM_REVIEW.HRM_REVIEW_ID=HRM_REVIEW_LINE.HRM_REVIEW_ID " +
						" WHERE HRM_REVIEW.ISACTIVE='Y' \n " +
							" AND HRM_REVIEW.AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							" AND HRM_REVIEW_LINE.HRM_COMPENSATIONTYPE='3' \n " +
							" AND HRM_REVIEW.DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				
				rs = pstmt.executeQuery();	
				while(rs.next()){
					result = new Mhrmreviewline(Env.getCtx(), rs, null);
					list.add(result);					
				}				
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return list.toArray(new Mhrmreviewline[list.size()]);		
	}
	

	public static X_hrm_reviewline getline(int hrm_review_id, int C_BPartner_ID, int C_Charge_ID){
		
		if (hrm_review_id==0)
			return null;

		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_hrm_reviewline result = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_REVIEWLINE \n " +
						"WHERE ISACTIVE='Y' \n " +
//							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_CHARGE_ID=" + C_Charge_ID + " \n " +
							"AND hrm_review_id=" + hrm_review_id + " \n " +
							"AND C_BPartner_ID =" + C_BPartner_ID ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					result = new X_hrm_reviewline(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return result;		
	}
}
